package com.hy.dao.impl;
/*
 * @ Date: 2024/5/14 22:34
 * @ Description: InstoretableDaoImpl
 **/

import com.hy.bean.Instoretable;
import com.hy.dao.InstoretableDao;
import com.hy.dto.InStoreSelectDto;
import com.hy.exception.BizException;
import com.hy.utils.DBManager;
import com.hy.vo.InStoreVo;
import org.apache.commons.dbutils.*;
import org.apache.commons.dbutils.handlers.*;
import org.apache.commons.lang3.StringUtils;

import java.lang.reflect.Field;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

public class InstoretableDaoImpl implements InstoretableDao {

    private final QueryRunner queryRunner = new QueryRunner();
    BasicRowProcessor basicRowProcessor = new BasicRowProcessor(new GenerousBeanProcessor());

    @Override
    public List<InStoreVo> queryAllByLimit(Integer currPage, Integer pageSize, InStoreSelectDto inStoreSelectDto) {
        Connection connection = null;
        String sql = "with t1 as(\n" +
                "\tselect\n" +
                "\t\tstore.id id,\n" +
                "\t\tstore.inStorePerson inStorePerson,\n" +
                "\t\tstore.inStoreTime inStoreTime,\n" +
                "\t\temp.employeeName employeeName,\n" +
                "\t\tcar.partName partName,\n" +
                "\t\tcar.partUnit partUnit,\n" +
                "\t\tcar.partPlaceCode partPlaceCode,\n" +
                "\t\tpar.inStoreCount inStoreCount,\n" +
                "\t\tproceduretb.producerName producerName,\n" +
                "\t\tcar.costPrice costPrice,\n" +
                "\t\tround(par.inStoreCount * car.costPrice,3) zje,\n" +
                "\t\tbuy.buyTime buyTime,\n" +
                "\t\tbuy.reachTime reachTime,\n" +
                "\t\tstore.inStoreNumber inStoreNumber,\n" +
                "\t\tstore.buyTable_id buyTable_id\n" +
                "\tfrom instoretable store\n" +
                "\tjoin buytable buy on store.buyTable_id = buy.id\n" +
                "\tjoin instore_partstable par on par.inStoreTable_id = store.id\n" +
                "\tjoin carpartstable car on par.partTable_id = car.id\n" +
                "\tjoin producertable proceduretb on car.producerTable_id = proceduretb.id\n" +
                "\tjoin employeetable emp on store.inStorePerson = emp.id " +
                "\t\n" +
                "),\n" +
                "t2 as (\n" +
                "\tselect buyTable_id,MIN(inStoreCount) inStoreCountMin from buy_parttable GROUP BY 1 ORDER BY 1\n" +
                ")\n" +
                "\n" +
                "select \n" +
                "\t\tt1.id id,\n" +
                "\t\tt1.inStorePerson inStorePerson,\n" +
                "\t\tt1.employeeName,\n" +
                "\t\tt1.inStoreTime inStoreTime,\n" +
                "\t\tt1.partName partName,\n" +
                "\t\tt1.partUnit partUnit,\n" +
                "\t\tt1.partPlaceCode partPlaceCode,\n" +
                "\t\tt1.inStoreCount inStoreCount,\n" +
                "\t\tt1.producerName producerName,\n" +
                "\t\tt1.costPrice costPrice,\n" +
                "\t\tt1.zje,\n" +
                "\t\tt1.buyTime buyTime,\n" +
                "\t\tt1.reachTime reachTime,\n" +
                "\t\tt1.inStoreNumber inStoreNumber,\n" +
                "\t\tt2.inStoreCountMin\n" +
                "from t1 join t2 on t1.buyTable_id = t2.buyTable_id where 1=1";

        try {
            connection = DBManager.getConnection();
            Map<String, Object> sql1 = createSql(inStoreSelectDto, sql);
            String sql2 = sql1.get("sql").toString();
            sql1.remove("sql");
            return queryRunner.query(connection, sql2, new BeanListHandler<>(InStoreVo.class, basicRowProcessor), (currPage - 1) * pageSize, pageSize);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    private Map<String, Object> createSql(InStoreSelectDto inStoreSelectDto, String sql) {
        Map<String, Object> map = new HashMap<>();
        if (!StringUtils.isBlank(inStoreSelectDto.getInStorePerson())) {
            map.put("inStorePerson", inStoreSelectDto.getInStorePerson());
        } if (inStoreSelectDto.getInStoreTime() != null) {
            map.put("inStoreTime", inStoreSelectDto.getInStoreTime());
        } if (inStoreSelectDto.getInStoreNumber() != null) {
            map.put("inStoreNumber", inStoreSelectDto.getInStoreNumber());
        }
        Map<String, Object> mapsql = new HashMap<>();

        Class aClass = inStoreSelectDto.getClass();
        Field[] declaredFields = aClass.getDeclaredFields();
        String s = null;
        String s2 = null;
        for (Field declaredField : declaredFields) {
            if (map.get(declaredField.getName()) != null) {
                s = declaredField.getName();
                s2 = map.get(declaredField.getName()).toString();
                if (s.equals("inStoreTime")) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    Date date = new Date(s2);
                    s2 = sdf.format(date);
                }
                sql += " and t1." + s + "= '" + s2 + "'";
            }
        }
        sql += " limit ?,?";
        mapsql.put("sql", sql);
        return mapsql;
    }

    @Override
    public long count(InStoreSelectDto inStoreSelectDto) {
        Connection connection = null;
        String sql = "with t1 as(\n" +
                "\tselect\n" +
                "\t\tstore.id id,\n" +
                "\t\tstore.inStorePerson inStorePerson,\n" +
                "\t\tstore.inStoreTime inStoreTime,\n" +
                "\t\temp.employeeName employeeName,\n" +
                "\t\tcar.partName partName,\n" +
                "\t\tcar.partUnit partUnit,\n" +
                "\t\tcar.partPlaceCode partPlaceCode,\n" +
                "\t\tpar.inStoreCount inStoreCount,\n" +
                "\t\tproceduretb.producerName producerName,\n" +
                "\t\tcar.costPrice costPrice,\n" +
                "\t\tround(par.inStoreCount * car.costPrice,3) zje,\n" +
                "\t\tbuy.buyTime buyTime,\n" +
                "\t\tbuy.reachTime reachTime,\n" +
                "\t\tstore.inStoreNumber inStoreNumber,\n" +
                "\t\tstore.buyTable_id buyTable_id\n" +
                "\tfrom instoretable store\n" +
                "\tjoin buytable buy on store.buyTable_id = buy.id\n" +
                "\tjoin instore_partstable par on par.inStoreTable_id = store.id\n" +
                "\tjoin carpartstable car on par.partTable_id = car.id\n" +
                "\tjoin producertable proceduretb on car.producerTable_id = proceduretb.id\n" +
                "\tjoin employeetable emp on store.inStorePerson = emp.id " +
                "\t\n" +
                "),\n" +
                "t2 as (\n" +
                "\tselect buyTable_id,MIN(inStoreCount) inStoreCountMin from buy_parttable GROUP BY 1 ORDER BY 1\n" +
                ")\n" +
                "\n" +
                "select count(*)count from t1 join t2 on t1.buyTable_id = t2.buyTable_id where 1=1";
        try {
            connection = DBManager.getConnection();
            Map<String, Object> sql1 = createSql2(inStoreSelectDto, sql);
            String sql2 = sql1.get("sql").toString();
            sql1.remove("sql");
            return queryRunner.query(connection, sql2, new ScalarHandler<>("count"));
        } catch (Exception e) {
            throw new BizException("Dao异常", e);
        } finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    private Map<String, Object> createSql2(InStoreSelectDto inStoreSelectDto, String sql) {
        Map<String, Object> map = new HashMap<>();
        if (!StringUtils.isBlank(inStoreSelectDto.getInStorePerson())) {
            map.put("inStorePerson", inStoreSelectDto.getInStorePerson());
        } if (inStoreSelectDto.getInStoreTime() != null) {
            map.put("inStoreTime", inStoreSelectDto.getInStoreTime());
        } if (inStoreSelectDto.getInStoreNumber() != null) {
            map.put("inStoreNumber", inStoreSelectDto.getInStoreNumber());
        }
        Map<String, Object> mapsql = new HashMap<>();

        Class aClass = inStoreSelectDto.getClass();
        Field[] declaredFields = aClass.getDeclaredFields();
        String s = null;
        String s2 = null;
        for (Field declaredField : declaredFields) {
            if (map.get(declaredField.getName()) != null) {
                s = declaredField.getName();
                s2 = map.get(declaredField.getName()).toString();
                if (s.equals("inStoreTime")) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    Date date = new Date(s2);
                    s2 = sdf.format(date);
                } else if (s.equals("inStoreNumber")) {
                    s = "buyTable_id";
                }
                sql += " and t1." + s + "= '" + s2 + "'";
            }
        }
        mapsql.put("sql", sql);
        return mapsql;
    }

    @Override
    public int insert(Connection connection,Instoretable instoretable) {
        String sql = "insert into instoretable(inStoreNumber,buyTable_id,inStorePerson,inStoreTime,remark) values(?,?,?,?,?)";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            BigInteger insert = queryRunner.insert(connection,sql,new ScalarHandler<>(),instoretable.getInstorenumber(),instoretable.getBuytableId(),instoretable.getInstoreperson(),sdf.format(instoretable.getInstoretime()),instoretable.getRemark());
            return insert.intValue();
        } catch (SQLException e) {
            throw new BizException("插入入库表失败",e);
        }
    }

    @Override
    public int update(Instoretable instoretable) {
        return 0;
    }

    @Override
    public int deleteById(Integer id) {
        return 0;
    }

    @Override
    public Instoretable selectByInStoreTime(Integer buyId) {
        Connection connection = null;
        String sql = "select * from instoretable where buyTable_id = ?";
        try {
            connection = DBManager.getConnection();
            return queryRunner.query(connection,sql,new BeanHandler<>(Instoretable.class),buyId);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}
